﻿''' minimal SQL*PLUS in IronPython
    - no size limitations at all, it all started with the need to output CLOB values containing
      Cariage Return, therefore the atypical select output format

    - SQL-Statements are executed after double Return
    - show and set need no extra Return
    - to cancel input type cancel Return
    
    single statements Create, insert, update, delete works
    Select work [but not with cursor expressions]
        
    simple PLS-Blocks [no parameter] seem to work
    
    describe [procedure parameters only] requiers a horible wrapper around dbms_describe.describe_procedure
        [SQL-Developer and SQL*PLUS don't need this]

    todo:
        spool,
        EXEC,
        dbms_output (is it possible to get this via ADO.NET ? )
        parameter binding

    Bernd Kriszio: 2006-11-04 
'''

import clr
import sys
clr.AddReference("System.Data")
##clr.AddReference("System.Data.OracleClient")
clr.AddReference("Oracle.DataAccess")

##import System
import System.Data
##import System.Data.OracleClient
import Oracle.DataAccess.Client

class ConnectionProperties :
    
    def set(self, string):
        self._dict = {}
        for i in string.split(';'):
            k, v = i.split('=')
            self._dict[k.lower()] = v

    
    def __getitem__(self, key):
        return self._dict[key]
        
        
def n2onoff(val):
    if val:
        return 'ON'
    else:
        return 'OFF'

class Connection:
    def queryConnectInfo(self):
        dataSource = raw_input('Data Source: ')
        user = raw_input('Username: ')
        password = raw_input('password: ')
        return 'Data Source=%s;User ID=%s;Password=%s' % (dataSource, user, password)

    def __init__(self, ConnectString = ''):
        self._echo = 0
        self._feedback = 0
        self._prompt = '>'
        self._spool = None
        self._spooldest = ''
        if not ConnectString:
            ConnectString = self.queryConnectInfo()
        self.Open(ConnectString)
            

    def Set(self, OptionString):
        ''' some resamblance to SQL*PLUS
            Tablemode on|off|auto
        '''
        s = OptionString.upper().strip()
        if s == 'ECHO ON':
            self._echo = 1
        elif s == 'ECHO OFF':
            self._echo = 0
        elif s == 'FEEDBACK ON':
            self._feedback = 1
        elif s == 'FEEDBACK OFF':
            self._feedback = 0
    
    def Show(self, OptionString):
        ''' when there are more options i'll work it out, meanwhile SHOW ALL
        '''
        print 'ECHO is ' + n2onoff(self._echo)
        print 'FEEDBACK is ' + n2onoff(self._echo)

    
    def Open(self, ConnectString):
        self._ConnectString = ConnectString
        self._ConnectionProperties = ConnectionProperties()
        self._ConnectionProperties.set(self._ConnectString)
        self._Connection = Oracle.DataAccess.Client.OracleConnection(self._ConnectString)
        self._Connection.Open()

        print '\nServerVersion: ' + self._Connection.ServerVersion
        print '\nConnection state: %s' % self._Connection.State.ToString()
        
    def Close(self):
        self._Connection.Close()

    def getConnection(self):
        return self._Connection

    def setCommand(self, query):
        self._Command = Oracle.DataAccess.Client.OracleCommand(query, self._Connection)    

    def ExecuteReader(self):
        self._Reader = self._Command.ExecuteReader()
##        bei SELECT immer -1
##        if self._feedback == 1:
##            print self._Reader.RecordsAffected
        
    def ExecuteNonQuery(self):
        self._count = self._Command.ExecuteNonQuery()
        if self._feedback == 1:
            print '%d rows affected' % self._count

    def ExecSql(self, query, mode = 0):
        if self._echo:
            print '> ' + query
        try:
##        if 1:
            if query.strip()[:6].upper() == 'SELECT':
                self.setCommand(query)
                self.ExecuteReader()
                self.print_Reader(mode)
##                print 'mode = %s' % mode
            elif query.strip()[:5].upper() == 'EXEC ':
                statement = query[5:]
                print 'statement %s' % statement
                proc = statement.split(' ')[0]
                print 'proc %s' % proc
            elif query.strip()[:5].upper() == 'DESC ':
##                print '--> desc'
                statement = query[5:]
                self.desc(statement.upper().strip())
            else:
##                print '--> else'
                self.setCommand(query)
                self.ExecuteNonQuery()
##        if 0:
        except StandardError, e:
            if self._echo == 0:  
                print query
            print "Fehler: ", e

    def hasTable(self, name):
        query = "SELECT * FROM USER_TABLES WHERE TABLE_NAME = '" + name + "'"
        self.setCommand(query)
        self.ExecuteReader()
        if self._Reader.Read():
            return 1
        else:
            return 0

    def getReader(self):
        return self._Reader

    def desc(self, object_name):
        ''' needs types and procedure from tst_desc_06.sql
        '''
        print '[desc %s]' %  object_name
        self.setCommand('META_BK_DESC1')
        
        param = Oracle.DataAccess.Client.OracleParameter('MYREFCURSOR', Oracle.DataAccess.Client.OracleDbType.RefCursor);
        param.Direction = System.Data.ParameterDirection.Output;
        self._Command.Parameters.Add(param);

        param = Oracle.DataAccess.Client.OracleParameter('P_OBJNAME', Oracle.DataAccess.Client.OracleDbType.Varchar2);
        param.Direction = System.Data.ParameterDirection.Input;
        param.Value = object_name
        self._Command.Parameters.Add(param);

        self._Command.CommandType = System.Data.CommandType.StoredProcedure
        self.ExecuteReader()
        self.print_Reader()
        

    def print_Reader (self, mode = 0 ):
        rdr =  self._Reader 
        anz = rdr.FieldCount
        print
        cnt = 0
        while rdr.Read():
            cnt += 1
            for i in range(anz):
                if mode == 0:
    ##            print '%-30s %-30s%s' % (rdr.GetName(i), rdr.GetFieldType(i), rdr[i])
                    print '%-30s %s' % (rdr.GetName(i), rdr[i])
                    if self._spool:
                         self._spool.write('---- Feld: %s -----\n' %  rdr.GetName(i))
                         self._spool.write('%s\n' % rdr[i])
                elif mode == 1:
                     print '%s' % (rdr[i].rstrip())
                    
            if mode == 0:
                print
        rdr.Close()
        self._count = cnt
        if self._feedback == 1:
            print '%d rows affected' % self._count

    def ufi(self):
        ''' nur für single line commands
        '''
        self._echo = 0
        while 1:
            a = raw_input(self._prompt)
            if a.lower() == 'quit':
                break
            self.ExecSql(a)

    def prompt1(self):
        return '%s@%s -->>' % (self._ConnectionProperties['user id'], self._ConnectionProperties['data source']) 

    def ufi2(self):
        ''' multi line commands
            I would like to use cancel ^C for cancel,
            but KeyboardInterrupt works somewhat different in IronPython
        '''
        cmd = ''
        print self.prompt1()
        while 1:
            a = raw_input()
            try:
                a_lc = a.lstrip().lower().split()[0]
            except:
                a_lc = ''
            if a_lc == 'cancel':
                print 'input canceld'
                cmd = ''
                print self.prompt1()
            elif a_lc == 'sp_helptext':
                query = "select text from USER_SOURCE where name = '%s' order by type, line" %  ' '.join(a.lstrip().split()[1:2]).upper()
                print query
                self.ExecSql(query, 1)
            elif a_lc == 'set':
                self.Set(' '.join(a.lstrip().split()[1:]))
            elif a_lc == 'show':
                self.Show(' '.join(a.lstrip().split()[1:]))
            elif a_lc == 'spool':
                dest = ' '.join(a.lstrip().split()[1:])
                if not dest:
                    if self._spooldest:
                        print 'spooling to %s' % self._spooldest
                    else:
                        print 'currently not spooling'
                else:
                    if self._spool:
                        self._spool.close()
                        self._spool = None
                        self._spooldest = ''
                    
                    if dest.strip().lower() != 'off':
                        self._spooldest = dest
                        self._spool = open(self._spooldest, 'w')
                    
            elif a_lc == 'connect':
##                print 'a:', a
                p = ' '.join(a.lstrip().split()[1:])
##                print 'p: ', p
                try:
                    (up, dataSource) = p.split('@', 1)
                except:
                    up = p
                    dataSource = self._ConnectionProperties['data source']

                print up, dataSource
                
                try:
                    user, password  = up.split('/',1)
                except:
                    user = up
                    password = raw_input('password: ')
                connectString = 'Data Source=%s;User ID=%s;Password=%s' % (dataSource, user, password)
                print '--> connect %s' % connectString
                self.Close()
                self.Open(connectString)
            else:    
                cmd += a + '\n'  
                if cmd.lower() == 'quit\n':
                    break
                if a == '' and cmd.strip():
##                    print 'calling: %s' %  cmd 
                    self.ExecSql(cmd)
                else:
                    continue
            cmd = ''
            print self.prompt1()
        
## ----- Self test call -------------------------------------------------------------------------    	        
if __name__=='__main__':

    try:
        import site_dbinfo
        connectStr = site_dbinfo.ora_connects[0]
    except:
         connectStr = "Data Source=XE;User ID=scott;Password=tiger"
##    connectStr = "Data Source=XE;User ID=scott;Password=tiger"
    print connectStr

    if 1:
        conn = Connection(connectStr)
    else:
        conn = Connection()
        
    conn.Set('Echo on')
    conn.Set('Feedback on')

    conn.ufi2()
    conn.Close()

##  select text from USER_SOURCE where name = upper(':pkg_name') order by type, line;
